﻿CREATE procedure [dbo].[sp_ReportReconPTSPearl] --exec sp_ReportReconPTSPearl 0, '2014-09-26'
	@yearPosition int,
	@date datetime
AS

DECLARE @year int
SET @year = YEAR(@date) + @yearPosition

DECLARE @previousYear int
SET @previousYear = @year - 1

DECLARE @query varchar(max) , @query2 varchar(max) 

SET @query = 'declare @lastDatePrevious datetime
select @lastDatePrevious = MAX(ReferenceDate) from BookOfWork' + CONVERT(varchar(4), @previousYear) + '

SELECT 
	FinancialType.AccountLevel1 as [Account Level 1]
	, FinancialType.AccountLevel3 as [Account Level 3]
	, System6Name as [System 6 Name]
	, SystemGocCode as [System Goc Code]
	, LobParent as [Lob Parent]
	, IsNull(case FinancialType.AccountLevel1 WHEN ''Cap'' then 0 ELSE SUM(currentBow.January) end, 0) as [System January Bow]
	, IsNull(case FinancialType.AccountLevel1 WHEN ''Cap'' THEN SUM(currentBow.January) ELSE SUM(currentBow.February) END, 0) as [System February Bow]
	, IsNull(case FinancialType.AccountLevel1 WHEN ''Cap'' THEN SUM(currentBow.February) ELSE SUM(currentBow.March) END, 0) as [System March Bow]
	, IsNull(case FinancialType.AccountLevel1 WHEN ''Cap'' THEN SUM(currentBow.March) ELSE SUM(currentBow.April) END, 0) as [System April Bow]
	, IsNull(case FinancialType.AccountLevel1 WHEN ''Cap'' THEN SUM(currentBow.April) ELSE SUM(currentBow.May) END, 0) as [System May Bow]
	, IsNull(case FinancialType.AccountLevel1 WHEN ''Cap'' THEN SUM(currentBow.May) ELSE SUM(currentBow.June) END, 0) as [System June Bow]
	, IsNull(case FinancialType.AccountLevel1 WHEN ''Cap'' THEN SUM(currentBow.June) ELSE SUM(currentBow.July) END, 0) as [System July Bow]
	, IsNull(case FinancialType.AccountLevel1 WHEN ''Cap'' THEN SUM(currentBow.July) ELSE SUM(currentBow.August) END, 0) as [System August Bow]
	, IsNull(case FinancialType.AccountLevel1 WHEN ''Cap'' THEN SUM(currentBow.August) ELSE SUM(currentBow.September) END, 0) as [System September Bow]
	, IsNull(case FinancialType.AccountLevel1 WHEN ''Cap'' THEN SUM(currentBow.September) ELSE SUM(currentBow.October) END, 0) as [System October Bow]
	, IsNull(case FinancialType.AccountLevel1 WHEN ''Cap'' THEN SUM(currentBow.October) ELSE SUM(currentBow.November) END, 0) as [System November Bow]
	, IsNull(case FinancialType.AccountLevel1 WHEN ''Cap'' THEN SUM(currentBow.November) ELSE SUM(currentBow.December) END, 0) as [System December Bow]
	, CASE WHEN Month(GetDate()) > 1 THEN IsNull(case FinancialType.AccountLevel1 WHEN ''Cap'' then 0 ELSE SUM(currentBow.January) END, 0) ELSE 0 END
	+ CASE WHEN Month(GetDate()) > 2 THEN IsNull(case FinancialType.AccountLevel1 WHEN ''Cap'' THEN SUM(currentBow.January) ELSE SUM(currentBow.February) END, 0) ELSE 0 END
	+ CASE WHEN Month(GetDate()) > 3 THEN IsNull(case FinancialType.AccountLevel1 WHEN ''Cap'' THEN SUM(currentBow.February) ELSE SUM(currentBow.March) END, 0) ELSE 0 END
	+ CASE WHEN Month(GetDate()) > 4 THEN IsNull(case FinancialType.AccountLevel1 WHEN ''Cap'' THEN SUM(currentBow.March) ELSE SUM(currentBow.April) END, 0) ELSE 0 END
	+ CASE WHEN Month(GetDate()) > 5 THEN IsNull(case FinancialType.AccountLevel1 WHEN ''Cap'' THEN SUM(currentBow.April) ELSE SUM(currentBow.May) END, 0) ELSE 0 END
	+ CASE WHEN Month(GetDate()) > 6 THEN IsNull(case FinancialType.AccountLevel1 WHEN ''Cap'' THEN SUM(currentBow.May) ELSE SUM(currentBow.June) END, 0) ELSE 0 END
	+ CASE WHEN Month(GetDate()) > 7 THEN IsNull(case FinancialType.AccountLevel1 WHEN ''Cap'' THEN SUM(currentBow.June) ELSE SUM(currentBow.July) END, 0) ELSE 0 END
	+ CASE WHEN Month(GetDate()) > 8 THEN IsNull(case FinancialType.AccountLevel1 WHEN ''Cap'' THEN SUM(currentBow.July) ELSE SUM(currentBow.August) END, 0) ELSE 0 END
	+ CASE WHEN Month(GetDate()) > 9 THEN IsNull(case FinancialType.AccountLevel1 WHEN ''Cap'' THEN SUM(currentBow.August) ELSE SUM(currentBow.September) END, 0) ELSE 0 END
	+ CASE WHEN Month(GetDate()) > 10 THEN IsNull(case FinancialType.AccountLevel1 WHEN ''Cap'' THEN SUM(currentBow.September) ELSE SUM(currentBow.October) END, 0) ELSE 0 END
	+ CASE WHEN Month(GetDate()) > 11 THEN IsNull(case FinancialType.AccountLevel1 WHEN ''Cap'' THEN SUM(currentBow.October) ELSE SUM(currentBow.November) END, 0)  ELSE 0 END
	+ CASE WHEN Month(GetDate()) > 12 THEN IsNull(case FinancialType.AccountLevel1 WHEN ''Cap'' THEN SUM(currentBow.November) ELSE SUM(currentBow.December) END, 0)  ELSE 0 END as [YTD Amount]
	, LineType as [PTS Line]
	, ValidGocs.OTHead as [O&T Head]
FROM BookOfWork' + CONVERT(varchar(4), @year) + ' currentBow
	INNER JOIN FinancialType on currentBow.FinancialTypeId = FinancialType.Id
	--LEFT JOIN GOC as SystemGOC on SystemGOC.GocAttributes = currentBow.SystemGocCode 
	INNER JOIN [ftools.nam.nsroot.net].GocAttributes.dbo.vwGocRuleResultHierarchy ValidGocs on SystemGocCode = ValidGocs.GocAttribute
	INNER JOIN  [ftools.nam.nsroot.net].GocAttributes.dbo.ManagedGeography ManagedGeography ON ManagedGeography.Id = ManagedGeographyId
WHERE currentBow.ReferenceDate = ''' + CONVERT(varchar(20), @date) + '''
	AND BowFlag = ''BOW''
	AND LobParent IN (''ICG Tech'',''GFTS'')
	AND FinancialType.AccountLevel3 <> ''FTE''
	AND ManagedGeography.Code = ''BR''
GROUP BY FinancialType.AccountLevel1 
	, FinancialType.AccountLevel3 
	, System6Name 
	, SystemGocCode
	, LobParent 
	, LineType  
	, ValidGocs.OTHead'

	
SET @query2 = ' UNION 
select FinancialType.AccountLevel1 as [Account Level 1]
, FinancialType.AccountLevel3 as [Account Level 3]
, System6Name as [System 6 Name]
, SystemGocCode as [System Goc Code]
, LobParent as [Lob Parent]
, case FinancialType.AccountLevel1 When ''Cap'' then SUM(ISNULL(previousBow.December, 0)) else 0 end as [System January Bow]
, 0 as [System February Bow]
, 0 as [System March Bow]
, 0 as [System April Bow]
, 0 as [System May Bow]
, 0 as [System June Bow]
, 0 as [System July Bow]
, 0 as [System August Bow]
, 0 as [System September Bow]
, 0 as [System October Bow]
, 0 as [System November Bow]
, 0 as [System December Bow]
, case FinancialType.AccountLevel1 When ''Cap'' then SUM(ISNULL(previousBow.December, 0)) else 0 end as [YTD Amount]
, LineType as [PTS Line]
, ValidGocs.OTHead as [O&T Head]
from BookOfWork' + CONVERT(varchar(4), @previousYear) + ' previousBow
INNER JOIN FinancialType on previousBow.FinancialTypeId = FinancialType.Id
	--LEFT JOIN GOC as SystemGOC on SystemGOC.GocAttributes = previousBow.SystemGocCode 
	INNER JOIN [ftools.nam.nsroot.net].GocAttributes.dbo.vwGocRuleResultHierarchy ValidGocs on SystemGocCode = ValidGocs.GocAttribute
	INNER JOIN  [ftools.nam.nsroot.net].GocAttributes.dbo.ManagedGeography ManagedGeography ON ManagedGeography.Id = ManagedGeographyId
WHERE previousBow.ReferenceDate = @lastDatePrevious
	AND BowFlag = ''BOW''
	AND LobParent IN (''ICG Tech'',''GFTS'')
	AND FinancialType.AccountLevel3 <> ''FTE''
	AND ManagedGeography.Code = ''BR''
GROUP BY FinancialType.AccountLevel1 
	, FinancialType.AccountLevel3 
	, System6Name 
	, SystemGocCode
	, LobParent 
	, LineType  
	, ValidGocs.OTHead'
	
EXEC(@query + ' ' + @query2)